import pandas as pd
from sklearn.cluster import Birch

# 读取csv文件
df = pd.read_csv('order_train_yu.csv')

# 计算每种商品的平均销售价格
avg_price = df.groupby('item_code')['item_price'].mean().reset_index()
avg_price.columns = ['item_code', 'avg_price']

# 计算每种商品的总销售量
total_qty = df.groupby('item_code')['ord_qty'].sum().reset_index()
total_qty.columns = ['item_code', 'total_qty']

# 计算每种商品的总销售额
total_sales = df.groupby('item_code')['ord_qty', 'item_price'].apply(lambda x: (x['ord_qty'] * x['item_price']).sum()).reset_index()
total_sales.columns = ['item_code', 'total_sales']

# 计算每种商品的销售频率
sales_frequency = df.groupby('item_code')['order_date'].nunique().reset_index()
sales_frequency.columns = ['item_code', 'sales_frequency']

# 计算每种商品每月的总销售额
monthly_sales = df.groupby(['item_code', 'year', 'month'])['ord_qty', 'item_price'].apply(lambda x: (x['ord_qty'] * x['item_price']).sum()).reset_index()
monthly_sales.columns = ['item_code', 'year', 'month', 'monthly_sales']

# 计算每种商品的销售增长率
monthly_sales['last_month_sales'] = monthly_sales.groupby('item_code')['monthly_sales'].shift()
monthly_sales['sales_growth_rate'] = (monthly_sales['monthly_sales'] - monthly_sales['last_month_sales']) / monthly_sales['last_month_sales']
sales_growth_rate = monthly_sales.groupby('item_code')['sales_growth_rate'].mean().reset_index()

# 计算每种商品每个季度的总销售额
df['quarter'] = df['month'].apply(lambda x: (x - 1) // 3 + 1)
quarterly_sales = df.groupby(['item_code', 'year', 'quarter'])['ord_qty', 'item_price'].apply(lambda x: (x['ord_qty'] * x['item_price']).sum()).reset_index()
quarterly_sales.columns = ['item_code', 'year', 'quarter', 'quarterly_sales']

# 计算每种商品的季节性
seasonality = quarterly_sales.groupby(['item_code', 'quarter'])['quarterly_sales'].mean().reset_index()
seasonality = seasonality.pivot(index='item_code', columns='quarter', values='quarterly_sales').reset_index()
seasonality.columns = ['item_code', 'q1_sales', 'q2_sales', 'q3_sales', 'q4_sales']

# 将所有特征合并到一个DataFrame中
features = pd.merge(avg_price, total_qty, on='item_code', how='left')
features = pd.merge(features, total_sales, on='item_code', how='left')
features = pd.merge(features, sales_frequency, on='item_code', how='left')
features = pd.merge(features, sales_growth_rate, on='item_code', how='left')
features = pd.merge(features, seasonality, on='item_code', how='left')

# 使用Birch算法进行聚类
birch = Birch(n_clusters=3)
features['cluster'] = birch.fit_predict(features[['avg_price', 'total_qty', 'total_sales', 'sales_frequency']])

# 将结果保存到CSV文件中
features.to_csv('clustering_results2.csv', index=False)
